﻿<cfsilent>
	<cfscript>
		
		sessionAdvice = getProperty("serviceFactory").getBean("sessionAdvice");

		temFile = GetTempDirectory() & createUUID() & ".xls";
		
		termId = event.getArg("TermID", event.getArg("TID"));
		
		sql = "SELECT academic_year, academic_term FROM t_term WHERE term_id = :termId ";
		
		queryObj = new query( datasource=application.dnsSlave ); 
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" ); 
		
		rs_term = queryObj.execute( sql=sql ).getResult(); 
		
		termstart = event.getArg("DateStart");
		
		depId = sessionAdvice.getUserProp("teacherDepartment");

		downFile = URLEncodedFormat( rs_term.academic_year & "-" & rs_term.academic_term & "补考课程安排", "utf-8");

		/* 生成电子表格数据 */
		sql = "SELECT 
					t_institute.institute_name, 
					t_course.course_name, 
					t_course.course_credit, 
					CASE WHEN t_course.course_code IS NULL THEN '-' ELSE t_course.course_code END course_code, 
					t_resittask.task_exrstid, 
					t_resittask.wid, 
					t_resittask.wdidx, 
					t_examcell.cellidx, 
					t_examcell.time_start, 
					t_examcell.time_end, 
					t_classroom.classroom, 
					t_classroom.cr_floor, 
					t_building.b_name, 
					COUNT(*) countStu 
				FROM 
					t_student_resit 
					INNER JOIN t_resittask ON t_resittask.task_exrstid = t_student_resit.task_exrstid 
					INNER JOIN t_course ON t_course.cid = t_resittask.cid 
					INNER JOIN t_institute ON t_institute.institute_id = t_course.institute_id 
					INNER JOIN t_examcell ON t_examcell.cellidx = t_resittask.cellidx 
					INNER JOIN t_classroom ON t_classroom.cr_id = t_student_resit.cr_id 
					INNER JOIN t_building ON t_building.b_id = t_classroom.b_id 
				WHERE 
					t_resittask.term_id = :termId 
					AND 
					t_resittask.task_prog = '3' 
					AND 
					t_institute.institute_id = :depID 
				GROUP BY 
					t_institute.institute_name, 
					t_course.course_name, 
					t_course.course_credit, 
					course_code, 
					t_resittask.task_exrstid, 
					t_resittask.wid, 
					t_resittask.wdidx, 
					t_examcell.cellidx, 
					t_examcell.time_start, 
					t_examcell.time_end, 
					t_classroom.classroom, 
					t_classroom.cr_floor, 
					t_building.b_name 
				ORDER BY 
					t_institute.institute_name, 
					t_resittask.wid, 
					t_resittask.wdidx, 
					t_course.course_name ";
				  
		queryObj = new query( datasource=application.dnsSlave ); 
		queryObj.addParam( name="termId", value=termId, cfsqltype="cf_sql_char" ); 
		queryObj.addParam( name="depID", value=depID, cfsqltype="cf_sql_char" ); 
		
		rs_exam = queryObj.execute( sql=sql ).getResult(); 

		
		/* 载入相关操作组件 */
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
		mathAdvice = getProperty("serviceFactory").getBean("senateMathAdvice");
		datetimeAdvice = getProperty("serviceFactory").getBean("datetimeAdvice");
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("补考课程及考场", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "任课单位,课程,学分,课程号,考试编号,考试时间,考试周次,场次安排,起止时间,考场,教学楼,所在楼层,参考人数");
		
		
		/* 写入考场安排 */
		for ( row=1; row LTE rs_exam.recordCount; row++ ) {
		
			
			rowData = rs_exam["institute_name"][row]
						& "," & rs_exam["course_name"][row] 
						& "," & numberFormat( rs_exam["course_credit"][row], "_.__" ) 
						& "," & rs_exam["course_code"][row] 
						& "," & rs_exam["task_exrstid"][row] 
						& "," & datetimeAdvice.formatDateString( mathAdvice.revertTaskWeekDay( termstart, rs_exam["wid"][row], rs_exam["wdidx"][row] ), "YYYY年MM月DD日") & " (" & datetimeAdvice.formatTimeString( rs_exam["time_start"][row], "HH:MI" ) & "~" & datetimeAdvice.formatTimeString( rs_exam["time_end"][row], "HH:MI" ) & ")"
						& "," & rs_exam["wid"][row]
						& "," & mathAdvice.convertWeekDayToChinese( rs_exam["wdidx"][row] ) & mathAdvice.convertExamIdxToChinese( rs_exam["cellidx"][row] ) 
						& "," & datetimeAdvice.formatTimeString( rs_exam["time_start"][row], "HH:MI" ) & "~" & datetimeAdvice.formatTimeString( rs_exam["time_end"][row], "HH:MI" )
						& "," & rs_exam["classroom"][row] 
						& "," & rs_exam["b_name"][row] 
						& "," & dictAdvice.getFloorString( rs_exam["cr_floor"][row] ) 
						& "," & rs_exam["countStu"][row];
			
			excel.SpreadsheetAddrow(spreadsheetObj, rowData); 
			
		}
	
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
	
	</cfscript>
	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />
</cfsilent>